package com.example.micro_property_springboot.mapper;

import com.example.micro_property_springboot.pojo.AssetInfo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * 资产统计分析Mapper接口
 * 用于执行统计查询相关的SQL操作
 */
@Mapper
public interface AssetStatisticsMapper {

    /**
     * 获取资产总数
     */
    @Select("SELECT COUNT(*) FROM asset_info")
    Long getTotalAssetCount();

    /**
     * 获取总资产价值
     */
    @Select("SELECT COALESCE(SUM(total_price), 0) FROM asset_info")
    Double getTotalAssetValue();

    /**
     * 按状态统计资产数量
     */
    @Select("SELECT status, COUNT(*) as count FROM asset_info GROUP BY status")
    List<Object> getAssetStatusDistribution();

    /**
     * 按部门统计资产数量和价值
     */
    @Select("SELECT ai.dept_id as deptId, sd.dept_name as deptName, COUNT(*) as count, COALESCE(SUM(ai.total_price), 0) as totalValue FROM asset_info ai LEFT JOIN sys_dept sd ON ai.dept_id = sd.id GROUP BY ai.dept_id, sd.dept_name ORDER BY count DESC")
    List<Object> getAssetDeptDistribution();

    /**
     * 按分类统计资产数量和价值
     */
    @Select("SELECT ai.category_id as categoryId, ac.category_name as categoryName, COUNT(*) as count, COALESCE(SUM(ai.total_price), 0) as totalValue FROM asset_info ai LEFT JOIN asset_category ac ON ai.category_id = ac.id GROUP BY ai.category_id, ac.category_name ORDER BY count DESC")
    List<Object> getAssetCategoryDistribution();

    /**
     * 获取近N天新增资产数量
     */
    @Select("SELECT COUNT(*) FROM asset_info WHERE create_time >= DATE_SUB(NOW(), INTERVAL #{days} DAY)")
    Long getNewAssetsCount(@Param("days") Integer days);

    /**
     * 获取近N天报废资产数量
     */
    @Select("SELECT COUNT(*) FROM asset_info WHERE status = '报废' AND update_time >= DATE_SUB(NOW(), INTERVAL #{days} DAY)")
    Long getScrapAssetsCount(@Param("days") Integer days);

    /**
     * 获取资产价值趋势数据
     */
    @Select("SELECT DATE_FORMAT(create_time, '%Y-%m-%d') as date, COALESCE(SUM(total_price), 0) as value FROM asset_info WHERE create_time BETWEEN #{startDate} AND #{endDate} GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d') ORDER BY date")
    List<Object> getAssetValueTrend(@Param("startDate") String startDate, @Param("endDate") String endDate);

    /**
     * 查询资产台账列表（分页）
     */
    @Select("SELECT ai.*, sd.dept_name, ac.category_name, su.real_name as user_name FROM asset_info ai LEFT JOIN sys_dept sd ON ai.dept_id = sd.id LEFT JOIN asset_category ac ON ai.category_id = ac.id LEFT JOIN sys_user su ON ai.user_id = su.id ORDER BY ai.create_time DESC")
    List<AssetInfo> getAssetLedgerList();

    /**
     * 查询资产流转报表数据
     * 这里简化处理，实际需要关联多个表
     */
    @Select("SELECT ai.asset_code, ai.name, ai.status, sd.dept_name as current_dept, su.real_name as current_user, ai.update_time FROM asset_info ai LEFT JOIN sys_dept sd ON ai.dept_id = sd.id LEFT JOIN sys_user su ON ai.user_id = su.id ORDER BY ai.update_time DESC")
    List<Map<String, Object>> getAssetTransferReport();

    /**
     * 查询部门领用热度数据
     */
    @Select("SELECT sd.dept_name, COUNT(*) as apply_count FROM asset_apply aa LEFT JOIN sys_dept sd ON aa.dept_id = sd.id WHERE aa.approve_status = 'APPROVED' GROUP BY aa.dept_id, sd.dept_name ORDER BY apply_count DESC")
    List<Map<String, Object>> getDeptUsageHotness();
}